package task;

import edu.buaa.act.helper.Utils;
import org.slf4j.Logger;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by song on 2017/6/12 0012.
 *
 * Note:
 * 1. 201501-201507,201603-201610 db not exist
 * 2. download speed is 100MB/10minutes
 *
 */
public class DownloadTrackData
{
    static Logger log = Utils.getDebugLogger();

    public static void main(String[] args){
        log.trace("{}", args);

        try {
            Connection connection = DriverManager.getConnection("jdbc:sqlserver://219.234.86.15\\SQL2008:1488;user=sa;password=82339812;databaseName=MainHisdata_201508");
            BufferedWriter writer = new BufferedWriter(new FileWriter(args[0]));

            int year = Integer.parseInt(args[1]);
            int month = Integer.parseInt(args[2]);

            getAllData(connection, writer, year, month);

            connection.close();
            writer.close();
        }catch (Throwable e){
            e.printStackTrace();
        }
    }

    public static void getAllData(Connection connection, BufferedWriter writer, int year, int month) throws SQLException, IOException {
        String dbName = String.format("MainHisdata_%d%02d", year, month);
        connection.setCatalog(dbName);
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(30);  // set timeout to 3 sec.
        ResultSet result = statement.executeQuery("SELECT TABLE_NAME FROM "+dbName+".INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
        Pattern r = Pattern.compile("HisData\\d{6}_(\\d+)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        List<String> tableList = new ArrayList<>();
        while(result.next()){
            Matcher m = r.matcher(result.getString(1));
            if (m.find()){
                String tableName = m.group(0);
                tableList.add(tableName);
            }
        }
        result.close();
        for(String tableName : tableList){
            Matcher m = r.matcher(tableName);
            if (m.find()){
                int carId = Integer.parseInt(m.group(1));
                getOneCarData(connection, writer, tableName, carId);
            }
        }
    }

    private static void getOneCarData(Connection connection, BufferedWriter writer, String tableName, int carId) throws SQLException, IOException {
        Statement statement = connection.createStatement();
        statement.setQueryTimeout(160);  // set timeout to 3 sec.
        ResultSet rs = statement.executeQuery("SELECT GD_time, GD_Latitude, GD_Longitude FROM "+tableName+" ORDER BY GD_time ASC");
        long count = 0;
        while(rs.next()){
            Timestamp time = rs.getTimestamp(1);
            float latitude = rs.getInt(2)/1000_000f;
            float longitude = rs.getInt(3)/1000_000f;
            writer.write(String.valueOf(carId));writer.write(",");
            writer.write(String.valueOf(time.getTime()));writer.write(",");
            writer.write(String.valueOf(latitude));writer.write(",");
            writer.write(String.valueOf(longitude));writer.write("\n");
            if(count%1_000_000==0){
                log.trace("write {} lines, current car {}", count, carId);
            }
            count++;
        }
    }
}
